<?php
/**
 * Events Model
 * @author 		TAMDV
 * @date		2008 07 28 (yyyy MM dd)
 * @package    	Joomus.JBS
 * @subpackage 	Components
 * @link 		http://localhost/jbschedule/administrator/index.php?option=com_jbschedule
 * @license		GNU/GPL
 */

// no direct access
//defined('_JEXEC') or die('Restricted access');


jimport ( 'joomla.application.component.model' );

/**
 * Events Model
 *
 * @package    Joomla.JBS
 * @subpackage Components
 */
class JBSModelEvents extends JModel {

	/**
	 * EventTypes data array
	 *
	 * @var array
	 */
	public $_data = null;

	/**
	 * Events total
	 *
	 * @var integer
	 */
	public $_total = null;

	/**
	 * Pagination object
	 *
	 * @var object
	 */
	public $_pagination = null;

	/**
	 * Default consturtor
	 *
	 *
	 */
	function __construct() {
		parent::__construct ();

		global $mainframe, $option;

		$limit = $mainframe->getUserStateFromRequest ( $option . 'limit', 'limit', $mainframe->getCfg ( 'list_limit' ), 'int' );
		$limitstart = $mainframe->getUserStateFromRequest ( $option . 'limitstart', 'limitstart', 0, 'int' );

		$this->setState ( 'limit', $limit );
		$this->setState ( 'limitstart', $limitstart );

		$array = JRequest::getVar ( 'cid', 0, '', 'array' );
		$this->setId ( ( int ) $array [0] );
	}

	/**
	 * Method to set the events identifier
	 *
	 * @access	public
	 * @param	int Events identifier
	 * @return	void
	 */
	function setId($id) {
		// Set id and wipe data
		$this->_id = $id;
		$this->_data = null;
	}

	/**
	 * Method to get event types item data
	 *
	 * @access public
	 * @return array
	 */
	function getData() {
		// Lets load the content if it doesn't already exist
		if (empty ( $this->_data )) {
			$query = $this->_buildQuery ();
//			echo $query;
			$this->_data = $this->_getList ( $query, $this->getState ( 'limitstart' ), $this->getState ( 'limit' ) );
		}
		//print_r($this->_data);


		return $this->_data;
	}
	
	
	/**
	 * Total nr of eventtypes
	 *
	 * @access public
	 * @return integer
	 */
	function getTotal() {
		// Lets load the total nr if it doesn't already exist
		if (empty ( $this->_total )) {
			$query = $this->_buildQuery ();
			$this->_total = $this->_getListCount ( $query );
		}

		return $this->_total;
	}

	/**
	 * Method to get a pagination object for the groups
	 *
	 * @access public
	 * @return integer
	 */
	function getPagination() {
		// Lets load the content if it doesn't already exist
		if (empty ( $this->_pagination )) {
			jimport ( 'joomla.html.pagination' );
			$this->_pagination = new JPagination ( $this->getTotal (), $this->getState ( 'limitstart' ), $this->getState ( 'limit' ) );
		}
		return $this->_pagination;
	}

	/**
	 * Build the query
	 *
	 * @access private
	 * @return string
	 */
	function _buildQuery() {
		// Get the WHERE and ORDER BY clauses for the query
		$where = $this->_buildContentWhere ();
		$orderby = $this->_buildContentOrderBy ();
		$query = "SELECT " .
		"e.id AS idevents," .
		"e.user_id," .
		"e.eventtype_id," .
		"e.start_date," .
		"e.end_date," .
		"e.start_time," .
		"e.end_time," .
		"e.color," .
		"e.use_master_color," .
		"e.is_allday_event," .
		"e.is_meeting," .
		"e.subject," .
		"e.content," .
		"e.published," .
		"e.ordering," .
		"e.recur_type," .
		"e.recur_daily_day," .
		"e.recur_daily_weekday," .
		"e.recur_weekly_week," .
		"e.recur_weekly_week_day," .
		"e.recur_monthly_month_day," .
		"e.recur_monthly_month_date," .
		"e.recur_yearly_year_month," .
		"e.recur_yearly_year_months," .
		"e.recur_monthly_option," .
		"e.recur_yearly_option," .
		"u.id," .
		"u.name," .
		"u.username," .
		"u.email," .
		"u.`password`," .
		"u.usertype," .
		"u.block," .
		"u.sendEmail," .
		"u.gid," .
		"u.registerDate," .
		"u.lastvisitDate," .
		"u.activation," .
		"u.params," .
		"et.id," .
		"et.title," .
		"et.color," .
		"et.description" .
		" FROM #__jbschedule_events e ".
		" Inner Join #__users u  ON e.user_id = u.id ".
		" Inner Join #__jbschedule_eventtypes et ON e.eventtype_id = et.id ";
		$query .= $where;
		$query .= $orderby;

		return $query;
	}

	/**
	 * Build the order clause
	 *
	 * @access private
	 * @return string
	 */
	function _buildContentOrderBy() {
		global $mainframe, $option;

		$nameSubject = 'e.start_date desc';

		$filter_order = $mainframe->getUserStateFromRequest ( $option . '.events.filter_order', 'filter_order', $nameSubject, 'cmd' );
		$filter_order_Dir = $mainframe->getUserStateFromRequest ( $option . '.events.filter_order_Dir', 'filter_order_Dir', '', 'word' );

		$orderby = ' ORDER BY ' . $filter_order . ' ' . $filter_order_Dir. ', '.$nameSubject;

		return $orderby;
	}

	/**
	 * Build the where clause
	 *
	 * @access private
	 * @return string
	 */
	function _buildContentWhere() {
		global $mainframe, $option;

		$search = $mainframe->getUserStateFromRequest ( $option . '.search', 'search', '', 'string' );
		$search = $this->_db->getEscaped ( trim ( JString::strtolower ( $search ) ) );

		$where = array ();
		// title là filed trong db
		$fieldSubject = 'subject';
		$where [] = ' LOWER(' . $fieldSubject . ') LIKE \'%' . $search . '%\' ';

		$where = (count ( $where ) ? ' WHERE ' . implode ( ' AND ', $where ) : '');


		$user = & JFactory::getUser();
		//check role
		$ok = JBSRoles :: checkPermission($this, JBSConfig :: $VIEW_ALL_EVENTS, null, null , false);
		if ($ok != 1){
			$where.= ' AND e.user_id ='.$user->get('id');
		}

		return $where;
	}

	function &getAllData() {
		if (empty ( $this->_data )) {
			$query = "SELECT " . "#__jbschedule_events.id AS idevents," . "#__jbschedule_events.user_id," . "#__jbschedule_events.eventtype_id," . "#__jbschedule_events.start_date," . "#__jbschedule_events.end_date," . "#__jbschedule_events.start_time," . "#__jbschedule_events.end_time," . "#__jbschedule_events.color," . "#__jbschedule_events.use_master_color," . "#__jbschedule_events.is_allday_event," . "#__jbschedule_events.is_meeting," . "#__jbschedule_events.subject," . "#__jbschedule_events.content," . "#__jbschedule_events.published," . "#__jbschedule_events.ordering," . "#__jbschedule_events.recur_type," . "#__jbschedule_events.recur_daily_day," . "#__jbschedule_events.recur_daily_weekday," . "#__jbschedule_events.recur_weekly_week," . "#__jbschedule_events.recur_weekly_week_day," . "#__jbschedule_events.recur_monthly_month_day," . "#__jbschedule_events.recur_monthly_month_date," . "#__jbschedule_events.recur_yearly_year_month," . "#__jbschedule_events.recur_yearly_year_months," . "#__jbschedule_events.recur_monthly_option," . "#__jbschedule_events.recur_yearly_option," . "#__users.id," . "#__users.name," . "#__users.username," . "#__users.email," . "#__users.`password`," . "#__users.usertype," . "#__users.block," . "#__users.sendEmail," . "#__users.gid," . "#__users.registerDate," . "#__users.lastvisitDate," . "#__users.activation," . "#__users.params," . "#__jbschedule_eventtypes.id," . "#__jbschedule_eventtypes.title," . "#__jbschedule_eventtypes.color," . "#__jbschedule_eventtypes.description" . " FROM #__jbschedule_events Inner Join #__users ON #__jbschedule_events.user_id = #__users.id Inner Join #__jbschedule_eventtypes ON #__jbschedule_events.eventtype_id = #__jbschedule_eventtypes.id";
			$this->_data = $this->_getList ( $query );
		}
		return $this->_data;
	}

	function getAllAssetByEvent($eventId) {
		$query = 'SELECT ' . '#__jbschedule_assets.name ' . 'FROM ' . '#__jbschedule_events_assets ' . 'Inner Join #__jbschedule_assets ON #__jbschedule_assets.id = #__jbschedule_events_assets.asset_id ' . 'WHERE ' . '#__jbschedule_events_assets.event_id =' . $eventId;
		return $this->_getList ( $query );
	}
	// get all data event type
	function getAllEventTypes() {
		$query = 'SELECT * ' . ' FROM #__jbschedule_eventtypes';
		return $this->_getList ( $query );
	}

	// get all data assetgroups
	function getAllAssetgroups() {
		$query = 'SELECT * ' . ' FROM #__jbschedule_assetgroups';
		return $this->_getList ( $query );
	}

	function getAllAssetByIdGroup($idgroup) {
		$query = 'SELECT * ' . ' FROM #__jbschedule_assets WHERE assetgroup_id=' . $idgroup;
		return $this->_getList ( $query );
	}

	function getAllEventsAssetsBy($idevent) {
		$query = 'SELECT * ' . ' FROM #__jbschedule_events_assets WHERE event_id=' . $idevent;
		return $this->_getList ( $query );
	}
	
	//get all users
	function getAllUser($idevent){
		$query = 'SELECT u.id,u.username FROM #__users u '.
				 'WHERE u.id not in (select eu.user_id FROM #__jbschedule_events_users eu WHERE eu.event_id = '.$idevent.')';
		return $this->_getList( $query );		
	}
	
	//get all users
	function getAllUsers(){
		$query = 'SELECT u.id,u.username '.
				 'FROM #__users u';
		return $this->_getList( $query );		
	}
	
	// get all users id in jos_jbschedule_events_users by event_id
	function getAllUserByEventIdInEventUser($idevent){
		$query = 'SELECT * ' . ' FROM #__jbschedule_events_users WHERE event_id=' . $idevent;
		return $this->_getList ( $query );			
	}
	
	//get all users is in events user
	function getAllUserIsEventUser($idevent){
		$query = 'SELECT *  FROM #__jbschedule_users tu '.
				 'Inner Join #__users u ON tu.system_user_id = u.id '.
				 'Inner Join #__jbschedule_events_users eu ON eu.user_id = tu.system_user_id '.
				 'WHERE eu.event_id = '.$idevent;
		return $this->_getList( $query );		
	}

	/**
	 * Method to get a Events
	 * @return object with data
	 */
	function &getDataOne() {
		// Load the data
		if (empty ( $this->_data )) {
			$query = ' SELECT * FROM #__jbschedule_events ' . '  WHERE id = ' . $this->_id;
			$this->_db->setQuery ( $query );
			$this->_data = $this->_db->loadObject ();
		}
		if (! $this->_data) {
			$this->_data = new stdClass ( );
			$this->_data->id = 0;
			$this->_data->user_id = null;
			$this->_data->eventtype_id = null;
			$this->_data->start_date = null;
			$this->_data->end_date = null;
			$this->_data->start_time = null;
			$this->_data->end_time = null;
			$this->_data->color = null;
			$this->_data->use_master_color = null;
			$this->_data->is_allday_event = null;
			$this->_data->is_meeting = null;
			$this->_data->subject = null;
			$this->_data->content = null;
			$this->_data->published = null;
			$this->_data->ordering = null;
			$this->_data->recur_type = null;
			$this->_data->recur_daily_day = null;
			$this->_data->recur_daily_weekday = null;
			$this->_data->recur_weekly_week = null;
			$this->_data->recur_weekly_week_day = null;
			$this->_data->recur_monthly_month_day = null;
			$this->_data->recur_monthly_month_date = null;
			$this->_data->recur_yearly_year_month = null;
			$this->_data->recur_yearly_year_months = null;

		}
		return $this->_data;
	}

	/**
	 * Method to store a record
	 *
	 * @access	public
	 * @return	boolean	True on success
	 */
	function store() {
		//get row of table


		$data = JRequest::get ( 'post' );
		$user = JFactory::getUser ();

		//get var on form
		$user_id = $user->get ( 'id' );
		$eventtype_id = $data ['eventtype_id'];
		$start_date = $data ['start_date'];
		$end_date = $data ['end_date'];
		$start_time = $data ['start_time'];
		$end_time = $data ['end_time'];
		$color = $data ['color'];
		$use_master_color = $data ['use_master_color'];
		$is_allday_event = $data ['is_allday_event'];
		$is_meeting = "1";
		$subject = $data ['subject'];
		$content = $data ['content'];
		$published = "1";
		$ordering = "99";
		$recur_type = $data ['recurTypeOptions'];

		switch ($recur_type) {
			case "1" :
				echo "TAB DAILY" . "<br>";
				$recur_daily_day = $data ['recur_daily_day'];
				$recur_daily_weekday = $data ['r_recur_daily_weekday'];
				$recur_weekly_week = null;
				$recur_weekly_week_day = null;
				$recur_monthly_month_day = null;
				$recur_monthly_month_date = null;
				$recur_yearly_year_month = null;
				$recur_yearly_year_months = null;
				$recur_monthly_option = null;
				$recur_yearly_option = null;
				break;
			case "2" :
//				echo "TAB WEEKLY" . "<br>";
				$recur_daily_day = null;
				$recur_daily_weekday = null;
				$recur_weekly_week = $data ['recur_weekly_week'];
				$recur_weekly_week_day = "";
				foreach ( $data ['recur_weekly_week_day'] as $rr ) {
					$recur_weekly_week_day .= $rr . ",";
				}
				$recur_weekly_week_day = substr ( $recur_weekly_week_day, 0, strlen ( $recur_weekly_week_day ) - 1 );
				$recur_monthly_month_day = null;
				$recur_monthly_month_date = null;
				$recur_yearly_year_month = null;
				$recur_yearly_year_months = null;
				$recur_monthly_option = null;
				$recur_yearly_option = null;
				break;
			case "3" :
//				echo "TAB MONTHLY" . "<br>";
				$recur_daily_day = null;
				$recur_daily_weekday = null;
				$recur_weekly_week = null;
				$recur_weekly_week_day = null;
				$recur_monthly_month_day = $data ['recur_monthly_month_day_x'] . "," . $data ['recur_monthly_month_day_y'];
				$recur_monthly_month_date = $data ['recur_monthly_month_date_x'] . "," . $data ['recur_monthly_month_date_y'] . "," . $data ['recur_monthly_month_date_n'];
				;
				$recur_yearly_year_month = null;
				$recur_yearly_year_months = null;
				$recur_monthly_option = $data ['r_recur_monthly_option'];
				$recur_yearly_option = null;
				break;
			case "4" :
//				echo "TAB YEARLY" . "<br>";
				$recur_daily_day = null;
				$recur_daily_weekday = null;
				$recur_weekly_week = null;
				$recur_weekly_week_day = null;
				$recur_monthly_month_day = null;
				$recur_monthly_month_date = null;
				$recur_yearly_year_month = $data ['recur_yearly_year_month_x'] . "," . $data ['recur_yearly_year_month_y'];
				$recur_yearly_year_months = $data ['recur_yearly_year_months_x'] . "," . $data ['recur_yearly_year_months_y'] . "," . $data ['recur_yearly_year_months_n'];
				$recur_monthly_option = null;
				$recur_yearly_option = $data ['r_recur_yearly_option'];
				break;
		}

		$sql = "INSERT INTO #__jbschedule_events(user_id,eventtype_id,start_date,end_date,start_time,end_time,color,use_master_color,is_allday_event,is_meeting,subject,content,published,ordering,recur_type,recur_daily_day,recur_daily_weekday,recur_weekly_week,recur_weekly_week_day,recur_monthly_month_day,recur_monthly_month_date,recur_yearly_year_month,recur_yearly_year_months,recur_monthly_option,recur_yearly_option) VALUES (";
		$sqlvalue = "'" . $user_id . "','" . $eventtype_id . "','" . $start_date . "','" . $end_date . "','" . $start_time . "','" . $end_time . "','" . $color . "','" . $use_master_color . "','" . $is_allday_event . "','" . $is_meeting . "','" . $subject . "','" . $content . "','" . $published . "','" . $ordering . "','" . $recur_type . "','" . $recur_daily_day . "','" . $recur_daily_weekday . "','" . $recur_weekly_week . "','" . $recur_weekly_week_day . "','" . $recur_monthly_month_day . "','" . $recur_monthly_month_date . "','" . $recur_yearly_year_month . "','" . $recur_yearly_year_months . "','" . $recur_monthly_option . "','" . $recur_yearly_option . "')";

		$id = JRequest::getVar ( 'id', array (0 ), 'post', 'array' );

		$sqlUpdate = "UPDATE #__jbschedule_events SET " . "user_id = '" . $user_id . "', " . "eventtype_id = '" . $eventtype_id . "', " . "start_date = '" . $start_date . "', " . "end_date = '" . $end_date . "', " . "start_time = '" . $start_time . "', " . "end_time = '" . $end_time . "', " . "color = '" . $color . "', " . "use_master_color = '" . $use_master_color . "', " . "is_allday_event = '" . $is_allday_event . "', " . "is_meeting = '" . $is_meeting . "', " . "subject = '" . $subject . "', " . "content = '" . $content . "', " . "published = '" . $published . "', " . "ordering = '" . $ordering . "', " . "recur_type = '" . $recur_type . "', " . "recur_daily_day = '" . $recur_daily_day . "', " . "recur_daily_weekday = '" . $recur_daily_weekday . "', " . "recur_weekly_week = '" . $recur_weekly_week . "', " . "recur_weekly_week_day = '" . $recur_weekly_week_day . "', " . "recur_monthly_month_day = '" . $recur_monthly_month_day . "', " . "recur_monthly_month_date = '" . $recur_monthly_month_date . "', " . "recur_yearly_year_month = '" . $recur_yearly_year_month . "', " . "recur_yearly_year_months = '" . $recur_yearly_year_months . "', " . "recur_monthly_option = '" . $recur_monthly_option . "', " . "recur_yearly_option = '" . $recur_yearly_option . "' " . " WHERE id = " . $id [0];

		if ($id [0] == 0) {
			//ACTION NEW
			$this->_db->Execute ( $sql . $sqlvalue );
			$idnumber = $this->_db->Execute ( "select max(jos_jbschedule_events.id ) from jos_jbschedule_events" );
			$idauto = $idnumber->data [0] [0];
			for($i = 0; $i < count ( $data ['assetId'] ); $i ++) {
				$sqlAssetId = "INSERT INTO #__jbschedule_events_assets VALUES (" . $idauto . "," . $data ['assetId'] [$i] . ")";
				$this->_db->Execute ( $sqlAssetId );
			}
			//insert #__jbschedule_events_users
			$inUser = JRequest::getVar( 'tableOne', array(0), 'post', 'array' );
			if (count($inUser) > 0){
				for($j=0;$j<count($inUser);$j++){
					$sqlEventUser = "INSERT INTO #__jbschedule_events_users VALUES (".$idauto.",".$inUser[$j].")";
					$this->_db->Execute ( $sqlEventUser );
				}
			}
		} else {
			//ACTION UPDATE
			$sqlDeleteAsset = "DELETE FROM #__jbschedule_events_assets WHERE event_id = " . $id [0] . " AND asset_id = ";
			$findAllAsset = $this->_getList ( "SELECT * FROM #__jbschedule_events_assets WHERE event_id = " . $id [0] );
			print_r ( $findAllAsset );
			if (count ( $findAllAsset ) > 0) {
				foreach ( $findAllAsset as $fa ) {
					$this->_db->Execute ( $sqlDeleteAsset . $fa->asset_id );
				}
			}
			for($i = 0; $i < count ( $data ['assetId'] ); $i ++) {
				$sqlAssetId = "INSERT INTO #__jbschedule_events_assets VALUES (" . $id [0] . "," . $data ['assetId'] [$i] . ")";
				$this->_db->Execute ( $sqlAssetId );
			}
			
			//delete event user
			$arr = $this->getAllUserByEventIdInEventUser($id[0]);
			if (count($arr)){
				for ($k=0;$k<count($arr);$k++){
					$sqlDeleteEventUser = "DELETE FROM #__jbschedule_events_users WHERE event_id=".$id[0]." and user_id=".$arr[$k]->user_id;
					$this->_db->Execute ( $sqlDeleteEventUser );
				}
			}
			
			//insert #__jbschedule_events_users
			$inUser = JRequest::getVar( 'tableOne', array(0), 'post', 'array' );
			if (count($inUser) > 0){
				for($j=0;$j<count($inUser);$j++){
					$sqlEventUser = "INSERT INTO #__jbschedule_events_users VALUES (".$id[0].",".$inUser[$j].")";
					$this->_db->Execute ( $sqlEventUser );
				}
			}
			$this->_db->Execute ( $sqlUpdate );
		}
		
		
		//send email
		//$this->_sendNotifyMail('trungt@triax.com.vn');
		$inUser = JRequest::getVar( 'tableOne', array(0), 'post', 'array' );
		if (count($inUser) > 0){
			for($j=0;$j<count($inUser);$j++){
				$this->_sendNotifyMailByIdUsers($inUser[$j]);	
			}
		}	
		//test ok		
		return true;
	}

	/**
	 * Method to delete record(s)
	 *
	 * @access	public
	 * @return	boolean	True on success
	 */
	function delete() {
		$cids = JRequest::getVar ( 'cid', array (0 ), 'post', 'array' );
		if (count ( $cids )) {
			foreach ( $cids as $cid ) {
				$sqlDelete = "DELETE FROM #__jbschedule_events WHERE id=" . $cid;
				$this->_db->Execute ( $sqlDelete );
				$arrEventsAsset = $this->_getList ( "SELECT * FROM #__jbschedule_events_assets WHERE event_id = " . $cid );
				foreach ( $arrEventsAsset as $ae ) {
					$sqlDeleteEventsAsset = "DELETE FROM #__jbschedule_events_assets WHERE event_id=" . $cid . " AND asset_id=" . $ae->asset_id;
					$this->_db->Execute ( $sqlDeleteEventsAsset );
				}
			}
		}
		return true;
	}

	/**
	 * Sends notify email.
	 *
	 * @param	string	An e-mail address
	 * @return	bool	True on success/false on failure
	 */
	function _sendNotifyMail($email)
	{
		$JBSsettings = Utils::getSettings();

		$config		= &JFactory::getConfig();
		$uri		= &JFactory::getURI();
		$url		= JURI::root().'index.php?option=com_jbschedule';
		$sitename	= $config->getValue('sitename');

		// Set the e-mail parameters
		$from		= $JBSsettings->sender_address;
		$fromname	= $config->getValue('JBS Administrator');
		$subject	= JText::sprintf('JBS_EVENT_NOTIFY_EMAIL_TITLE', $sitename);
		$body		= JText::sprintf('JBS_EVENT_NOTIFY_EMAIL_TEXT', $sitename, $url);

		// Send the e-mail
		if (!JUtility::sendMail($from, $fromname, $email, $subject, $body))
		{
			$this->setError('ERROR_SENDING_CONFIRMATION_EMAIL');
			return false;
		}

		return true;
	}
	
	/**
	 * Sends notify email.
	 *
	 * @param	string	An e-mail address of id user
	 * @return	bool	True on success/false on failure
	 */
	function _sendNotifyMailByIdUsers($iduser)
	{
		//get email by user
		$sqlEmailofUser = "SELECT u.email FROM #__users u WHERE u.id =".$iduser;
		$arrUser = $this->_getList($sqlEmailofUser);
		$email = $arrUser[0]->email;
		
		$JBSsettings = Utils::getSettings();
		
		$config		= &JFactory::getConfig();
		$url		= JURI::root().'index.php?option=com_jbschedule';
		$sitename	= $config->getValue('sitename');

		// Set the e-mail parameters
		$from		= $JBSsettings->sender_address;
		$fromname	= $config->getValue('JBS Administrator');
		$subject	= JText::sprintf('JBS_EVENT_NOTIFY_EMAIL_TITLE', $sitename);
		$body		= JText::sprintf('JBS_EVENT_NOTIFY_EMAIL_TEXT', $sitename, $url);

		// Send the e-mail
		if (!JUtility::sendMail($from, $fromname, $email, $subject, $body))
		{
			$this->setError('ERROR_SENDING_CONFIRMATION_EMAIL');
			return false;
		}

		return true;
	}
	
}
?>